/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50728
 Source Host           : localhost:3306
 Source Schema         : intelligentlight

 Target Server Type    : MySQL
 Target Server Version : 50728
 File Encoding         : 65001

 Date: 22/03/2021 15:13:45
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for areas
-- ----------------------------
DROP TABLE IF EXISTS `areas`;
CREATE TABLE `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `areaname` varchar(255) NOT NULL COMMENT '区域名字',
  `parentid` int(11) DEFAULT NULL COMMENT '父区域 id',
  `status` smallint(6) DEFAULT '1' COMMENT '1有效 0 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of areas
-- ----------------------------
BEGIN;
INSERT INTO `areas` VALUES (1, '西城区', NULL, 1);
INSERT INTO `areas` VALUES (2, '城关街道', NULL, 1);
INSERT INTO `areas` VALUES (3, '龙泉路', 1, 1);
INSERT INTO `areas` VALUES (4, '兴隆路', 1, 1);
INSERT INTO `areas` VALUES (5, '冶源镇', NULL, 1);
INSERT INTO `areas` VALUES (6, '二七区', NULL, 1);
INSERT INTO `areas` VALUES (7, '航海路', 6, 1);
COMMIT;

-- ----------------------------
-- Table structure for categories
-- ----------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categoryname` varchar(255) NOT NULL COMMENT '分类名字',
  `commandsjson` varchar(2550) NOT NULL COMMENT '类型命令 json',
  `status` smallint(6) DEFAULT '1' COMMENT '1 有效 0 无效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of categories
-- ----------------------------
BEGIN;
INSERT INTO `categories` VALUES (1, '智能路灯', '{\"open\":{\"type\":1,\"value\":\"0\"},\"close\":{         \"type\":1,         \"value\":\"1\"     },      \"change\":{         \"type\":2,         \"value\":\"2\"     }  }', 1);
INSERT INTO `categories` VALUES (2, '引魂灯', '{\"open\":\"open\",\"close\":\"close\",\"dd\":\"diandong\",\"s55\":\"suiji\"}', 1);
COMMIT;

-- ----------------------------
-- Table structure for devices
-- ----------------------------
DROP TABLE IF EXISTS `devices`;
CREATE TABLE `devices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `devicesuuid` varchar(32) NOT NULL COMMENT '设备序列化',
  `devicename` varchar(255) NOT NULL COMMENT '设备名字',
  `addtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加日期',
  `address` varchar(255) NOT NULL COMMENT '所在位置',
  `areaid` int(11) NOT NULL COMMENT '所属区域 id',
  `direction` char(1) NOT NULL COMMENT '方向 0 左 右',
  `num` int(11) NOT NULL COMMENT '灯在路上的编号,比如左侧 1234567 右侧 1234567',
  `status` char(1) NOT NULL DEFAULT '3' COMMENT '状态,0离线,1 在线,2 损坏 3 待上线',
  `lastconnecttime` datetime DEFAULT NULL COMMENT '最后一次上线时间',
  `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `devices_devicesuuid_uindex` (`devicesuuid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of devices
-- ----------------------------
BEGIN;
INSERT INTO `devices` VALUES (1, '1234567890', 'ESP01S', '2020-12-13 15:23:19', '阴曹路 444 号', 3, '1', 1, '1', '2021-03-22 14:52:56', 1);
INSERT INTO `devices` VALUES (2, '1312332', '蓝牙灯', '2020-12-13 15:23:19', '冤鬼路', 4, '1', 2, '0', '2021-02-20 17:08:17', 2);
INSERT INTO `devices` VALUES (3, '312321sdaddssda', '蓝牙灯', '2020-12-13 15:23:19', '黄泉路', 4, '1', 3, '0', '2020-12-13 23:22:19', 1);
INSERT INTO `devices` VALUES (4, 'sdfsdfsdffs', '蓝牙灯', '2020-12-13 15:23:19', '冤鬼路', 4, '1', 4, '3', '2020-12-13 23:22:19', 1);
INSERT INTO `devices` VALUES (5, 'dsfsdvdsfsdfsdf', '蓝牙灯', '2020-12-13 15:23:19', '灵堂课室', 3, '1', 5, '0', '2020-12-13 23:22:19', 2);
INSERT INTO `devices` VALUES (6, '3453efefdfdsf43', '蓝牙灯', '2020-12-13 15:23:19', '衣柜', 3, '1', 6, '0', '2020-12-13 23:22:19', 1);
INSERT INTO `devices` VALUES (7, 'fdsfdsvdsfsdfsdf', '蓝牙灯', '2020-12-13 15:23:19', '床底下', 3, '1', 7, '0', '2020-12-13 23:22:19', 1);
INSERT INTO `devices` VALUES (8, '23rffsdfdsfsdf', '蓝牙灯', '2020-12-13 15:23:19', '门口', 4, '1', 8, '0', '2020-12-13 23:22:19', 2);
INSERT INTO `devices` VALUES (9, 'fdsfsdfdsfsdfsdvs', '蓝牙灯', '2020-12-13 15:23:19', '耳边', 3, '1', 9, '0', '2020-12-13 23:22:19', 1);
INSERT INTO `devices` VALUES (10, 'fsdfsdfsdfsdfs', '蓝牙灯', '2020-12-13 15:23:19', '窗户边', 4, '1', 10, '0', '2020-12-13 23:22:19', 1);
INSERT INTO `devices` VALUES (11, 'fsdvfsgwrtw4ggs', '蓝牙灯', '2020-12-13 15:23:19', '天花板', 3, '1', 11, '0', '2020-12-13 23:22:19', 2);
INSERT INTO `devices` VALUES (12, '234234', 'lanyadeng', '2020-12-17 16:32:43', '黑水路', 7, '1', 1, '3', NULL, 2);
COMMIT;

-- ----------------------------
-- Table structure for menus
-- ----------------------------
DROP TABLE IF EXISTS `menus`;
CREATE TABLE `menus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(200) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of menus
-- ----------------------------
BEGIN;
INSERT INTO `menus` VALUES (1, '/users.html', '用户列表');
INSERT INTO `menus` VALUES (2, '/categories.html', '设备分类');
INSERT INTO `menus` VALUES (3, '/areas.html', '区域列表');
INSERT INTO `menus` VALUES (4, '/devices.html', '设备列表');
COMMIT;

-- ----------------------------
-- Table structure for user_arean
-- ----------------------------
DROP TABLE IF EXISTS `user_arean`;
CREATE TABLE `user_arean` (
  `userid` int(11) DEFAULT NULL COMMENT '用户 id',
  `areanid` int(11) DEFAULT NULL COMMENT '管理区域 id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user_arean
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `lastlogin` datetime(6) DEFAULT NULL COMMENT '上次登录日期',
  `currentlogin` datetime(6) DEFAULT NULL COMMENT '本次登录日期',
  `type` char(1) DEFAULT NULL COMMENT '用户类型',
  `status` char(1) NOT NULL DEFAULT '1' COMMENT '状态 1 在职,0 离职',
  `data2` varchar(255) DEFAULT NULL COMMENT '备用字段',
  `data1` varchar(255) DEFAULT NULL COMMENT '备用字段',
  `regdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
INSERT INTO `users` VALUES (1, 'admin', '4311752135ccb0f05e5efac618de39d2', '2020-12-13 19:29:09.842000', '2020-12-13 19:29:09.842000', '1', '1', NULL, NULL, '2020-12-08 21:56:41');
INSERT INTO `users` VALUES (2, 'zhangsan', '9bad41710724cf6511abde2a52416881', NULL, NULL, '0', '1', NULL, NULL, '2020-12-08 23:00:55');
INSERT INTO `users` VALUES (3, 'lisi', '1b539b60601b934441308049a9526e7d', NULL, NULL, '0', '0', NULL, NULL, '2020-12-08 23:04:07');
INSERT INTO `users` VALUES (4, 'zhanfei', '36fcb6c911f452e395a357335fe5db95', NULL, NULL, '0', '1', NULL, NULL, '2020-12-11 09:52:05');
INSERT INTO `users` VALUES (5, 'lisi1', '5a323422cfb15b6a33cb0edab73ce034', NULL, NULL, '1', '1', NULL, NULL, '2020-12-14 11:33:51');
INSERT INTO `users` VALUES (6, 's', 'jfjfhfhj', NULL, NULL, '1', '1', NULL, NULL, '2020-12-14 15:43:27');
INSERT INTO `users` VALUES (7, 'fsdfsd', 'ffsdfsd', NULL, NULL, '1', '1', NULL, NULL, '2020-12-14 15:43:34');
INSERT INTO `users` VALUES (8, 'ffsdf', 'fsdfsd', NULL, NULL, '1', '1', NULL, NULL, '2020-12-14 15:43:39');
INSERT INTO `users` VALUES (9, 'fsfs', 'fdfs', NULL, NULL, '1', '1', NULL, NULL, '2020-12-14 15:43:45');
INSERT INTO `users` VALUES (10, 'fsdf', 'fsdf', NULL, NULL, '1', '1', NULL, NULL, '2020-12-14 15:43:50');
INSERT INTO `users` VALUES (11, 'fsdf', 'fsdfsd', NULL, NULL, '1', '1', NULL, NULL, '2020-12-14 15:43:54');
INSERT INTO `users` VALUES (12, 'wangruodi', 'makabaka', NULL, NULL, '0', '1', NULL, NULL, '2021-03-22 14:59:19');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
